# from django.test import TestCase
#
# # Create your tests here.
# # _types = ['通用','开发','测试','需求','运维','实施','综合/咨询']
# # s = ['general','develop','test','story','om','implement','ask']
# #
# # res = {}
# # for i,j in zip(_types,s):
# #     res[i] = j
# #
# # print(getattr(res,'通用'))

# import pandas as pd
# import numpy as np
#
# df_ls = pd.read_excel('/media/977GB/wcj_work/禅道/新视图开发/经营项目台账列表20241125.xlsx')
# df = pd.read_excel('/media/977GB/wcj_work/禅道/新视图开发/zentao/禅道数据统计_产品维度.xlsx')
#
# #获取需要的产品列表
# # df_need = df_ls[df_ls['项目状态'].isin(['待初验','待终验'])]
# df_need = df_ls[df_ls['项目类型'].isin(['开发实施类'])]
# print(df_need.shape)
# ls = df_need['项目名称']
# print(len(ls))
# print(len(set(ls)))
#
# ori = set(ls)
#
# data = []
#
# res = set()
#
# s = {'行政办公绿色低碳能源管理应用 V1.0建设项目', '2023年南方电网数字企业科技（广东）有限公司计财域技术改造项目', '电网管理平台（计财域V2.0-特殊资金应用）建设项目', '电网管理平台（人资域V2.0-人力资源共享服务等）建设项目', '易联（elink）办公系统租赁单一来源采购项目', '电网管理平台（年金管理应用V2.0-财务管理、账户运营管理等）建设项目', '法智平台建设项目', '电网管理平台（人资域V2.1-智慧化培训管理、干部管理提升等优化）建设项目'}
#
# res_i = {}
#
# for row in df.itertuples():
#     for i in ls:
#         if i in row[2]:
#             res.add(i)
#
#             if i not in res_i.keys():
#                 res_i[i] = []
#             res_i[i].append(row[2])
#
#             # print(i)
#             if i in s:
#                 print(i,row[2])
#
#             row = list(row)
#             data.append(row[1:]+[sum(row[-13:])])
#             break
#
# df_res = pd.DataFrame(columns=list(df.columns)+['使用情况'],data=data)
#
# def map1(x):
#     if x==0:
#         return '未使用'
#     else:
#         return '已使用'
#
# df_res['使用情况'] = df_res['使用情况'].map(map1)
#
# print(len(set(df_res['产品名称'])))
# print(len(df_res['产品名称']))
#
# print(ori-res)
# print(res-ori)
#
# for key,value in res_i.items():
#     if len(value) > 1:
#         print(key,value)
#
# df_res.to_excel('/media/977GB/wcj_work/禅道/新视图开发/筛选的产品.xlsx',index=False)
# # #
# # #
# # df['使用情况'] = df.iloc[:, -13:].sum(axis=1)
# # df['使用情况'] = df['使用情况'].map(map1)
# # #
# # df.to_excel('/media/ethony/Ubuntu 20.0/禅道/新视图开发/所有的产品.xlsx',index=False)
# #
# #
# #
# #
# # product = {}
# # for i in df_product.itertuples():
# #     if i[1] not in product.keys():
# #         product[i[1]] = 0
# #     product[i[1]] += 1
# #
# # for i in product.keys():
# #     if product[i] > person[i]:
# #         print(f'{i}\tperson: {person[i]}\tproduct: {product[i]}')
# #
# #
# #筛选人员
import pandas as pd
import re
from collections import Counter
from zentao.settings import STATISTIC_PERSON

df = pd.read_excel(STATISTIC_PERSON)

names = []
names_name = []
name_special = []
for i,j in zip(df['拟供应人员姓名'],df['单位（部门）']):
    names.append(j+'-'+i)
    names_name.append(i)
    if j == '计划财务部':
        name_special.append(i)

print(len(names))
names2 = list(df['拟供应人员姓名'])

# 使用Counter来计数
element_count = Counter(names2)

print(element_count)

df1 = pd.read_excel('/home/ethony/下载/公司-研发效能.xlsx',sheet_name='需求').iloc[1:]
df2 = pd.read_excel('/home/ethony/下载/公司-研发效能.xlsx',sheet_name='开发').iloc[1:]
df3 = pd.read_excel('/home/ethony/下载/公司-研发效能.xlsx',sheet_name='综合_咨询').iloc[1:]
df4 = pd.read_excel('/home/ethony/下载/公司-研发效能.xlsx',sheet_name='测试').iloc[1:]
df5 = pd.read_excel('/home/ethony/下载/公司-研发效能.xlsx',sheet_name='运维').iloc[1:]
df6 = pd.read_excel('/home/ethony/下载/公司-研发效能.xlsx',sheet_name='实施').iloc[1:]

df1['1111'] = df1.apply(lambda row: row['部门']+'-'+row['姓名']+'-'+row['用户名'], axis=1)
df2['1111'] = df2.apply(lambda row: row['部门']+'-'+row['姓名']+'-'+row['用户名'], axis=1)
df3['1111'] = df3.apply(lambda row: row['部门']+'-'+row['姓名']+'-'+row['用户名'], axis=1)
df4['1111'] = df4.apply(lambda row: row['部门']+'-'+row['姓名']+'-'+row['用户名'], axis=1)
df5['1111'] = df5.apply(lambda row: row['部门']+'-'+row['姓名']+'-'+row['用户名'], axis=1)
df6['1111'] = df6.apply(lambda row: row['部门']+'-'+row['姓名']+'-'+row['用户名'], axis=1)

names1 = list(df1['1111']) + list(df2['1111']) + list(df3['1111']) + list(df4['1111']) + list(df5['1111']) + list(df6['1111'])

accounts = set()

ls = []
cnt = 0
for name in names1:

    account = name.split('-')[-1]

    name = '-'.join(name.replace('（','(').replace(' ','').split('(')[0].split('-')[:2])

    # name = ''.join(re.findall(r'[\u4e00-\u9fa5]', name))

    # if '刘俊杰' in name:
    #     print(name)

    if name not in names:
        # 如果名字只有一个，则直接用名字判断
        s = name
        name = name.split('-')[1]
        if name not in ['杨柳', '陈丽婷', '王强', '刘鹏', '陈亮', '陈涛']:
            if name in names_name:
                print(s)
                # accounts.add(account)
                # ls.append(name)
                # cnt += 1
    else:
        ls.append(name)
        accounts.add(account)
        cnt += 1

print(cnt)
print(len(accounts))
cha = []

for i in names:

    if i not in ls:
        cha.append(i.split('-')[1])

print(cha)
print(len(accounts)+len(cha))
print(len(cha))

df = pd.DataFrame(columns=['姓名'],data = cha)
df.to_excel('/media/977GB/wcj_work/禅道/新视图开发/zentao/没有的人.xlsx',index=False)
# #
# # import pymysql
# # from zentao.settings import PYMYSQL_CONF
# #
# # string = ""
# # for i in cha:
# #     string += f"'{i}',"
# #
# # sql = f'''
# #     select realname from zt_user where realname in ({string[:-1]})
# #     '''
# # a = []
# # # pymysql连接数据
# # connection = pymysql.connect(**PYMYSQL_CONF)
# # # 执行 SQL 语句
# # # 连接数据库
# # with connection.cursor() as cursor:
# #     cursor.execute(sql)
# #     get_users = cursor.fetchall()
# #     for i in get_users:
# #         print(i[0],1111)
# #         a.append(i[0])
# #
# # print(len(get_users))
# # # for i in get_users:
# # #     print(i)
# # s  = []
# # for i in cha:
# #     sql = f'''
# #         select realname from zt_user where realname like '%{i}%'
# #         '''
# #     # pymysql连接数据
# #     connection = pymysql.connect(**PYMYSQL_CONF)
# #     # 执行 SQL 语句
# #     # 连接数据库
# #     with connection.cursor() as cursor:
# #         cursor.execute(sql)
# #         get_users = cursor.fetchall()
# #
# #     if len(get_users) != 0:
# #         if get_users[0][0] not in a:
# #             print(get_users, i)
# #         print(get_users)
# #         s.append(get_users)
# #
# # print(len(s))
# #
# # #查找所有用户
# # def get_user():
# #     sql = f'''
# #     SELECT
# #         u.account,
# #         u.realname,
# #         c.name AS company,
# #         d.name AS dept,
# #         u.role,
# #         u.deleted
# #     FROM
# #         zt_user AS u
# #     LEFT JOIN
# #         zt_company AS c ON u.company = c.id
# #     LEFT JOIN
# #         zt_dept AS d ON u.dept = d.id
# #     WHERE
# #         realname in ({string[:-1]})
# #     '''
# #     # pymysql连接数据
# #     connection = pymysql.connect(**PYMYSQL_CONF)
# #     # 执行 SQL 语句
# #     # 连接数据库
# #     with connection.cursor() as cursor:
# #         cursor.execute(sql)
# #         get_users = cursor.fetchall()
# #
# #     #将岗位转为岗位类型
# #     # position_map = {'开发': 'develop',
# #     #                 '开发负责人': 'develop',
# #     #                 '开发组长': 'develop',
# #     #                 '测试': 'test',
# #     #                 '测试负责人': 'test',
# #     #                 '测试组长': 'test',
# #     #                 '需求': 'story',
# #     #                 '需求负责人': 'story',
# #     #                 '需求组长': 'story',
# #     #                 '运维': 'om',
# #     #                 '实施': 'implement',
# #     #                 '咨询人员': 'ask',
# #     #                 '综合人员': 'ask'}
# #     position_map = {
# #         "imp":"实施",
# #         "om": "运维",
# #         "cp": "综合人员",
# #         "cst": "咨询人员",
# #         "qaz": "测试组长",
# #         "dez": "开发组长",
# #         "rez": "需求组长",
# #         "dev": "开发人员",
# #         "qa": "测试人员",
# #         "td": "开发负责人",
# #         "pd": "需求负责人",
# #         "qd": "测试负责人",
# #         "req": "需求人员"
# #     }
# #
# #     users = []
# #     account2role = {}
# #     for user in get_users:
# #
# #         # users.append(list(user))
# #         users.append({
# #             'account': user[0],
# #             'realname': user[1],
# #             'company': user[2],
# #             'dept': user[3],
# #             'positionType': user[4],
# #             'deleted':user[5]
# #         })
# #
# #     connection.close()
# #
# #     # df = pd.DataFrame(columns=['account', 'realname', 'company', 'dept', 'name','positionType'], data=users)
# #     df = pd.DataFrame(users)
# #     # df.to_csv('user.csv', index=False)
# #     df.to_csv('/media/977GB/wcj_work/禅道/新视图开发/zentao/人员.csv',index=False)
# #
# #     return users, account2role, df
# #
# # get_user()
# #
# # df1 = df1[df1['用户名'].isin(accounts)]
# # df2 = df2[df2['用户名'].isin(accounts)]
# # df3 = df3[df3['用户名'].isin(accounts)]
# # df4 = df4[df4['用户名'].isin(accounts)]
# # df5 = df5[df5['用户名'].isin(accounts)]
# # df6 = df6[df6['用户名'].isin(accounts)]
# #
# # # 将多个数据框写入同一个Excel文件的不同工作表中
# # with pd.ExcelWriter('/media/977GB/wcj_work/禅道/新视图开发/zentao/multiple_dfs.xlsx') as writer:
# #     df1.to_excel(writer, sheet_name='需求', index=False)
# #     df2.to_excel(writer, sheet_name='开发', index=False)
# #     df3.to_excel(writer, sheet_name='综合_咨询', index=False)
# #     df4.to_excel(writer, sheet_name='测试', index=False)
# #     df5.to_excel(writer, sheet_name='运维', index=False)
# #     df6.to_excel(writer, sheet_name='实施', index=False)
# #
# #
# #
# #
# # import pandas as pd
# #
# # df = pd.read_excel('/media/977GB/wcj_work/禅道/新视图开发/zentao/没有的人(复件).xlsx')
# #
# # depts = set(df['部门'])
# #
# # for dept in depts:
# #     df1 = df[df['部门']==dept]
# #
# #     df1.to_excel(f'/media/977GB/wcj_work/禅道/新视图开发/zentao/有问题/{dept}.xlsx',index=False)
#
#
#
# # #排名计算
# # import pandas as pd
# # import numpy as np
# # # 读取前两行数据
# # df_temp = pd.read_excel('/media/ethony/Ubuntu 20.0/禅道/新视图开发/数企研发效能（2024年1210）.xlsx', nrows=2,header=None,sheet_name='开发')
# #
# # # 合并两行作为列名，如果第二行中的元素为空，则使用第一行的元素
# # combined_headers = df_temp.iloc[1].fillna(df_temp.iloc[0])
# #
# # # 读取整个文件，跳过之前读取的两行
# # df = pd.read_excel('/media/ethony/Ubuntu 20.0/禅道/新视图开发/数企研发效能（2024年1210）.xlsx', skiprows=2, header=None,sheet_name='开发')
# # df.columns = combined_headers  # 应用新的列名
# #
# # #计算代码情况得分
# # def develop_code(x):
# #
# #     if x == 0:
# #         score = 0
# #     elif 0<x<0.5:
# #         score = 50 + 20 * x
# #     elif 0.5<=x<1:
# #         score = 60 + 20 * (x - 0.5)
# #     elif 1<=x<1.5:
# #         score = 70 + 20 * (x - 1)
# #     elif 1.5<=x<2:
# #         score = 80 + 40 * (x - 1.5)
# #     else:
# #         score = 100
# #
# #     return score
# #
# # def remark(data):
# #     # 计算规则
# #     if float(data) == 0:
# #         return f'未提交或者代码量为0得0分'
# #     elif 0 < float(data) < 0.5:
# #         return f'50 + 20 * {float(data)}'
# #     elif 0.5 <= float(data) < 1:
# #         return f'60 + 20 * ({float(data)} - 0.5)'
# #     elif 1 <= float(data) < 1.5:
# #         return f'70 + 20 * ({float(data)} - 1)'
# #     elif 1.5 <= float(data) < 2:
# #         return f'80 + 40 * ({float(data)} - 1.5)'
# #     else:
# #         return f'代码提交情况达到基准值2倍及以上得分=100'
# #
# # df['代码提交情况'] = df['提交有效代码数']/df['代码提交基准值D']
# #
# # df['代码提交情况'] = df['代码提交情况'].fillna(0).replace(np.inf, 0)
# # df['代码提交情况计算规则'] = df['代码提交情况'].map(remark)
# # df['提交有效代码数-得分'] = df['代码提交情况'].map(develop_code)
# # df['提交有效代码数-排名'] = df.groupby('部门')['提交有效代码数-得分'].rank(method='dense', ascending=False)
# # # 更新最大排名列
# # df['提交有效代码数-最大排名'] = df.groupby('部门')['提交有效代码数-排名'].transform('max')
# #
# # #开发人员
# # df['总分'] = \
# #     df['得分'] * 0.3 + \
# #     df['加分-得分'] * 0.15 - \
# #     df['减分-得分'] * 0.15 + \
# #     df['提交有效代码数-得分'] * 0.3 + \
# #     df['未关闭缺陷数-得分'] * 0.25
# #
# # df['1总分'] = \
# #     df['提交有效代码数-得分'] * 0.3 + \
# #     df['未关闭缺陷数-得分'] * 0.25
# #
# #
# # # 在每个部门内按总分进行排名，相同值时排名相同
# # df['部门岗位-排名'] = df.groupby(['部门','岗位类型'])['总分'].rank(method='dense', ascending=False)
# # # 在每个部门内按总分进行排名，相同值时排名相同
# # df['排名'] = df.groupby('部门')['总分'].rank(method='dense', ascending=False)
# #
# # df.to_excel('/media/977GB/wcj_work/禅道/新视图开发/zentao/111.xlsx')


# import json
# import re
#
# #取汉字直到非汉字
# import pandas as pd
#
#
# def get_zh(name):
#
#     result = ''
#
#     for i in name:
#         if '\u4e00' <= i <= '\u9fa5':
#             result += i
#         else:
#             break
#
#     return result
#
# def pinjie(account, name):
#     account = ''.join(re.findall(r'[a-zA-Z]', account))
#
#     return account + get_zh(name)
#
#
# with open('/media/977GB/wcj_work/禅道/新视图开发/git-all-2.json', 'r') as f:
#     response = json.load(f)
#
# df = pd.read_excel('/home/ethony/下载/公司-研发效能 (2).xlsx',sheet_name='开发')
# a = set()
# for account, name in zip(df['用户名'],df['姓名']):
#     if isinstance(account,str):
#         print([account,name])
#         a.add(pinjie(account,name))
#
# b = set()
# for detail in response['data']:
#     b.add(pinjie(detail['username'],detail['name']))
#
# c = a & b
#
# print(len(c))
#
# cnt = 0
# for account, name in zip(df['用户名'],df['姓名']):
#     if isinstance(account,str):
#         if pinjie(account,name) in c:
#             cnt += 1
#
# print(cnt)

# import json
#
# with open('/media/977GB/wcj_work/禅道/新视图开发/zentao/1.json','r') as f:
#     data = json.load(f)
#
#
# s = 0
# for key,value in data.items():
#     if 'chenjie' in key:
#         print(key,value)
#     if len(value) != 1:
#         print(key,value)
#         s += 1
# print(len(data))
# print(s)
